﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.IO;
using System.Data.Common;
using System.Data;

namespace ExportationUtils
{
    public class ExcelGenerator
    {
        //When dict is not null, the header row will have its cells translated from the property names.
        //Any property not in dict will not be written to the xls file
        public static void GenerateXlsTable(IList objs, Type type, string fileName, string sheetName,
            IDictionary<string, string> dict)
        {
            if (objs.Count < 1)
                return;
            StringBuilder table = new StringBuilder("<table>\n\t<tr>\n");

            //Writing the titles
            PropertyInfo[] props = type.GetProperties();
            if (dict == null)
            {
                for (int i = 0; i < props.Length; ++i)
                {
                    table.Append(string.Format("\t\t<td><b>{0}</b></td>\n", props[i].Name));
                }
            }
            else
            {
                for (int i = 0; i < props.Length; ++i)
                {
                    string propName = props[i].Name;
                    if (dict.ContainsKey(propName))
                        table.Append(string.Format("\t\t<td><b>{0}</b></td>\n", dict[propName]));
                    else
                        props[i] = null;
                }
            }
            table.Append("\t</tr>\n");

            //Writing the values
            for (int y = 0; y < objs.Count; ++y)
            {
                table.Append("\t<tr>\n");
                for (int x = 0; x < props.Length; ++x)
                {
                    if (props[x] == null)
                        continue;
                    object propertyValue = props[x].GetValue(objs[y], null);
                    string val = (propertyValue == null) ? "" : propertyValue.ToString();
                    table.Append(string.Format("\t\t<td>{0}</td>\n", val));
                }
                table.Append("\t</tr>\n");
            }
            table.Append("</table>");
            FileStream stream = new FileStream(fileName, FileMode.Create);
            StreamWriter writer = new StreamWriter(stream, Encoding.Unicode);
            writer.Write(table);
            writer.Close();
        }

        //When dict is not null, the header row will have its cells translated from the property names.
        //Any property not in dict will not be written to the xls file
        //Office 2003 xls format
        public static void GenerateBinaryXls(IList objs, Type type, string fileName, string sheetName,
            IDictionary<string, string> dict)
        {
            if (objs.Count < 1)
                return;

            FileInfo newFile = new FileInfo(fileName);
            if (newFile.Exists)
            {
                newFile.Delete();
            }

            string connectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=NO;""", fileName);
            DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;
                connection.Open();
                using (DbCommand command = connection.CreateCommand())
                {
                    //List of valid properties
                    IList<string> validProps = new List<string>();

                    //Creating the table
                    PropertyInfo[] props = type.GetProperties();
                    StringBuilder strCreateTable = new StringBuilder(string.Format("CREATE TABLE [{0}](", sheetName));
                    if (dict == null)
                    {
                        for (int i = 0; i < props.Length - 1; ++i)
                        {
                            strCreateTable.Append(string.Format("[{0}] text,", props[i].Name));
                        }
                        strCreateTable.Append(string.Format("[{0}] text", props[props.Length - 1].Name));
                    }
                    else
                    {
                        foreach (string dictKey in dict.Keys)
                        {
                            if (type.GetProperty(dictKey) == null)
                                continue;
                            strCreateTable.Append(string.Format("[{0}] text,", dict[dictKey]));
                            validProps.Add(dictKey);
                        }
                        //remove the final ','
                        strCreateTable.Remove(strCreateTable.Length - 1, 1);
                        strCreateTable.Append(")");
                    }
                    command.CommandText = strCreateTable.ToString();
                    command.ExecuteNonQuery();
                    //End Creating Table ***********************

                    for (int i = 0; i < props.Length; ++i)
                    {
                        if (!validProps.Contains(props[i].Name))
                            props[i] = null;
                    }

                    //Writing the values
                    for (int y = 0; y < objs.Count; ++y)
                    {
                        StringBuilder strInserValue = new StringBuilder(string.Format("INSERT INTO [{0}] VALUES(", sheetName));
                        object propertyValue;
                        string val;

                        foreach (string validProp in validProps)
                        {
                            propertyValue = type.GetProperty(validProp).GetValue(objs[y], null);
                            val = (propertyValue == null) ? "" : propertyValue.ToString();
                            strInserValue.Append(string.Format("\'{0}\',", val.Replace("'", "''")));
                        }

                        //remove the final ','
                        strInserValue.Remove(strInserValue.Length - 1, 1);
                        strInserValue.Append(")");

                        command.CommandText = strInserValue.ToString();
                        command.ExecuteNonQuery();
                    }
                }
                connection.Close();
            }
        }

    }
}
